package com.wenhua.mapper;

import com.wenhua.pojo.Student;
import org.apache.ibatis.annotations.*;

import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

@Mapper
public interface StudentMapper {




    /* 查询学生信息方法 */
    List<Student> selectStudent(
            Integer offset,//偏移量
            Integer pageSize,//每页数量
            String selectString,//查询字符串(查询dormitory_name,name,grade中必须包含相关字符串)
            String sort);//排序方式(比如desc)

    /* 查询学生出入记录方法 */
    List<Student> selectStudentMove(Integer offset, Integer pageSize, String selectString, String sort);

    /* 查询学生信息数量方法 */
    @Select("select count(*) from view_students_in_dormitory where dormitory_name LIKE CONCAT('%', #{selectString}, '%') or name LIKE CONCAT('%', #{selectString}, '%') or grade LIKE CONCAT('%', #{selectString}, '%') or student_number LIKE CONCAT('%', #{selectString}, '%')")
    Integer selectStudentCount(String selectString);//查询字符串(查询dormitory_name,name,grade中必须包含相关字符串)

    /* 查询学生出入记录数量方法 *//**/
    @Select("select count(*) from view_access_log_with_dormitory_and_student_info where dormitory_name LIKE CONCAT('%', #{selectString}, '%') or name LIKE CONCAT('%', #{selectString}, '%') or grade LIKE CONCAT('%', #{selectString}, '%') or student_number LIKE CONCAT('%', #{selectString}, '%')")
    Integer selectStudentMoveCount(String selectString);//查询字符串(查询dormitory_name,name,grade中必须包含相关字符串)

    /* 添加学生信息方法,返回值为添加成功后的id */
    Integer addStudent(Student student);

    /* 通过id查询学生 */
    @Select("select * from view_students_in_dormitory where student_id = #{studentId}")
    Student selectStudentById(Integer studentId);

    /* 更新学生信息方法 */
    @Update("UPDATE student_info SET student_number = #{studentNumber}, name = #{name}, gender = #{gender}, " +
            "age = #{age}, department = #{department}, grade = #{grade} WHERE student_id = #{studentId}")
    void updateStudent(Student student);

    /* 删除学生信息方法 */
    @Delete("DELETE FROM student_info WHERE student_id = #{id}")
    void deleteStudent(Integer id);

    /* 记录学生出入方法-只需要在access_log表中添加一条信息即可 */

    // 使用@Insert注解来定义SQL插入语句
    @Insert("INSERT INTO access_log (student_id, action, event_time) VALUES (#{studentId}, #{action}, #{eventTime})")
    void studentMove(Integer studentId, String action, Timestamp eventTime);


}
